package com.wzdigit.wms.common.utils;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.wzdigit.wms.basic.domain.util.ExportExcleDTO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;


/**
 * 多Sheet导出Excel工具类
 * Created by zyf on 2021/10/12 18:41
 */
@Slf4j
@Service
public class ExportExcelUtils<T> {

    /**
     * @param exportList
     *            Excle导出实体类
     * @param out
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    public  void exportExcel(List<ExportExcleDTO> exportList, OutputStream out) throws Exception{
        XSSFWorkbook workbook = null;
        try {
            // 声明一个工作薄
            workbook = new XSSFWorkbook();
            for(ExportExcleDTO<T> exportDTO :exportList){
                // 生成一个表格
                String dateParent=exportDTO.getPattern();
                if (dateParent==null){
                    dateParent="yyyy-MM-dd";
                }
                XSSFSheet sheet = workbook.createSheet(exportDTO.getTitle());
                // 设置表格默认列宽度为20个字节
                sheet.setDefaultColumnWidth(20);
                XSSFCellStyle style2 = workbook.createCellStyle();
                XSSFDataFormat df = workbook.createDataFormat();
                //设置边框为细边框
                style2.setBorderBottom(BorderStyle.THIN);
                style2.setBorderLeft(BorderStyle.THIN);
                style2.setBorderRight(BorderStyle.THIN);
                style2.setBorderTop(BorderStyle.THIN);

                // 产生表格标题行
                XSSFRow row = sheet.createRow(0);
                for (short i = 0; i < exportDTO.getHeaders().length; i++) {
                    XSSFCell cell = row.createCell(i);
                    XSSFCellStyle style3 = workbook.createCellStyle();
                    style3.setBorderBottom(BorderStyle.THIN);
                    style3.setBorderLeft(BorderStyle.THIN);
                    style3.setBorderRight(BorderStyle.THIN);
                    style3.setBorderTop(BorderStyle.THIN);
                    XSSFFont font = workbook.createFont();
                    font.setBold(true);//字体加粗
                    style3.setFont(font);
                    cell.setCellStyle(style3);
                    XSSFRichTextString text = new XSSFRichTextString(exportDTO.getHeaders()[i]);
                    cell.setCellValue(text);
                }

                // 遍历集合数据，产生数据行
                Iterator<T> it = exportDTO.getDataSet().iterator();
                int index = 0;
                while (it.hasNext()) {
                    index++;
                    row = sheet.createRow(index);
                    T t = (T) it.next();
                    // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
                    String typeName = t.getClass().getTypeName();
                    if("java.util.LinkedHashMap".equals(typeName)){
                        LinkedHashMap map = (LinkedHashMap) t;
                        int i=0;
                        for (Object key : map.keySet()) {
                            XSSFCell cell = row.createCell(i);
                            cell.setCellStyle(style2);
                            Object value = map.get(key);
                            setValue(exportDTO.getPattern(), exportDTO.getFormatType(), style2, df, cell, value,dateParent);
                            if (i==(exportDTO.getHeaders().length-1)){
                                break;
                            }
                            i++;
                        }
                    }else{
                        Field[] fields = t.getClass().getDeclaredFields();
                        for (short i = 0; i < exportDTO.getHeaders().length; i++) {
                            XSSFCell cell = row.createCell(i);
                            cell.setCellStyle(style2);
                            Field field = fields[i];
                            String fieldName = field.getName();

                            String getMethodName = "get"
                                    + fieldName.substring(0, 1).toUpperCase()
                                    + fieldName.substring(1);
                            try {
                                Class tCls = t.getClass();
                                Method getMethod = tCls.getMethod(getMethodName,
                                        new Class[] {});
                                Object value = getMethod.invoke(t, new Object[] {});
                                setValue(exportDTO.getPattern(), exportDTO.getFormatType(), style2, df, cell, value,dateParent);
                            } catch (Exception e) {
                                log.error("导出Excel异常，信息：" + e.getMessage());
                            }
                        }
                    }
                }

            }
            workbook.write(out);
        } catch (IOException e) {
            log.error("导出Excel异常，信息：" + e.getMessage());
            throw e;
        }finally{
            try {
                workbook.close();
            } catch (IOException e) {
                log.error("关闭workbook异常，信息：" + e.getMessage());
                throw e;
            }
        }
    }


    @SuppressWarnings("deprecation")
    private  void setValue(String pattern, String formatType,
                          XSSFCellStyle style2, XSSFDataFormat df, XSSFCell cell, Object value,String dateParent) {
        // 判断值的类型后进行强制类型转换
        String textValue = null;
        if (value instanceof Integer) {
            int intValue = (Integer) value;
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(intValue);
        } else if (value instanceof Float) {
            float fValue = (Float) value;
            XSSFRichTextString xfValue = new XSSFRichTextString(
                    String.valueOf(fValue));
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(xfValue);
        } else if (value instanceof Double) {
            double dValue = (Double) value;
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            //设置数字输出格式
            style2.setDataFormat(df.getFormat(formatType));
            cell.setCellStyle(style2);
            cell.setCellValue(dValue);
        } else if (value instanceof Long) {
            long longValue = (Long) value;
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(longValue);
        } else if (value instanceof Date) {
            Date date = (Date) value;
            SimpleDateFormat sdf = new SimpleDateFormat(dateParent);
            textValue = sdf.format(date);
        } else if(value instanceof String){
            cell.setCellValue(value+"");
        }else {
            // 其它数据类型都当作字符串简单处理
            if (value == null || "null".equals(value)) {
                textValue = "";
            } else {
                textValue = value + "";
            }
        }
        // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
        if (textValue != null) {
            //Pattern p = Pattern.compile("^//d+(//.//d+)?$");
            Pattern p = Pattern.compile("^[+-]?([1-9][0-9]*|0)(\\.[0-9]+)?$");
            Matcher matcher = p.matcher(textValue);
            if (matcher.matches()) {
                // 是数字当作double处理
                cell.setCellValue(Double.parseDouble(textValue));
            } else {
                XSSFRichTextString richString = new XSSFRichTextString(textValue);
                cell.setCellValue(richString);
            }
        }
    }



}
